package com.focus.xyz.core.common.generator;

import com.focus.xyz.core.utils.Constants;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

/**
 * Created by chenmeng on 2016/4/26.
 */
public class DataDriver implements IDriverSV {
    private static transient Logger log = LoggerFactory.getLogger(DataDriver.class);

    private String driver = null;
    private String url = null;
    private String username = null;
    private String pass = null;

    private Connection conn = null;

    private DataDriver() {

    }

    public DataDriver(String driver, String url, String username, String pass) {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.pass = pass;
        getConnection();
    }

 /*   public boolean execute(String sql) {
        boolean rtn = false;
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            rtn = statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("执行sql语句异常");
        }finally{
            closeConnection();
        }
        return rtn;
    }*/

    public void getConnection() {
        try {
            Class.forName(driver);
            if (StringUtils.isBlank(url)) {
                log.error("数据库连接串为空");
            } else if (StringUtils.isBlank(username)) {
                log.error("数据库用户名为空");
            } else if (StringUtils.isBlank(pass)) {
                log.error("数据库密码为空");
            } else {
                conn = DriverManager.getConnection(url, username, pass);
                //禁用自动提交
                conn.setAutoCommit(false);
            }
        } catch (Exception e) {
            log.error("数据库连接异常：" + e.getMessage());
        }
    }

    public Map<String, String> executeQuery(String sql) {
        Map<String, String> map = new HashMap<String, String>();
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.executeQuery();
            map.put(Constants.FLAG, Constants.SUCCESS);
        } catch (SQLException e) {
            map.put(Constants.FLAG, Constants.FAIL);
            map.put(Constants._EXCEPTION, e.getMessage());
            log.error("执行查询语句异常" + e);
        }
        return map;
    }

    public ResultSet executeQuery2(String sql) {
        ResultSet rs = null;
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            rs = statement.executeQuery();
        } catch (SQLException e) {
            log.error("执行查询语句异常" + e);
        }
        return rs;
    }

    public Map<String, String> executeUpdate(String sql) {
        Map<String, String> map = new HashMap<String, String>();
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.executeUpdate(sql);
            map.put(Constants.FLAG, Constants.SUCCESS);
        } catch (SQLException e) {
            map.put(Constants.FLAG, Constants.FAIL);
            map.put(Constants._EXCEPTION, e.getMessage());
            log.error("执行更新语句异常" + e);
        }
        return map;
    }

    public Map<String, String> execute(String sql) {
        Map<String, String> map = new HashMap<String, String>();
        if (StringUtils.startsWith(sql, "select")) {
            map = executeQuery(sql);
        } else {
            map = executeUpdate(sql);
        }
        return map;
    }

    public Map<String, String> executeSqlBlock(String sqlBlock) {
        Map<String, String> map = new HashMap<String, String>();
        CallableStatement cst = null;
        try {
            cst = conn.prepareCall(sqlBlock); // 执行存储过程
//			cst.registerOutParameter(1, Types.INTEGER); // 为存储过程设定返回值
            int count = cst.executeUpdate(); // 得到预编译语句更新记录或删除操作的结果
//			int id = cst.getInt(1); // 得到返回值
            map.put(Constants.FLAG, Constants.SUCCESS);
        } catch (SQLException e) {
            map.put(Constants.FLAG, Constants.FAIL);
            map.put(Constants._EXCEPTION, e.getMessage());
        }
        return map;
    }

    public void closeConnection() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                log.error("关闭数据库连接异常");
            }
        }
    }
    public void commit() {
        try {
            this.conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("提交事务异常");
        }
    }
    public void rollback() {
        try {
            this.conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("回滚事务异常");
        }
    }

    public String getSysDate(){
        String sql = "select to_char(sysdate,'yyyy-mm-dd') from dual";
        ResultSet rs = executeQuery2(sql);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String rtn = sdf.format(new Date(System.currentTimeMillis()));
        try{
            while(rs.next()){
                rtn = rs.getString(1);
            }
            closeConnection();
        }catch (SQLException e){

        }
        return rtn;
    }

    public String getNextDate(int n){
        StringBuffer sb = new StringBuffer();
        sb.append("select to_char(sysdate + " + n + ",'yyyy-mm-dd') from dual");
        ResultSet rs = executeQuery2(sb.toString());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String rtn = sdf.format(new Date(System.currentTimeMillis()));
        try{
            while(rs.next()){
                rtn = rs.getString(1);
            }
            closeConnection();
        }catch (SQLException e){

        }
        return rtn;
    }

    public static String transSqlStr(String source) {
        String[] searchList = new String[]{"to_date(", "TO_DATE(", "'yyyy-mm-dd hh24:mi:ss'", "sysdate", "SYSDATE", "'yyyy-mm-dd'", "'dd-mm-yyyy'", "'DD-MM-YYYY'", "'mm-dd-yyyy'", "'MM-DD-YYYY'", "'dd-mm-yyyy hh24:mi:ss'", "'DD-MM-YYYY HH24:MI:SS'", "'DD-MM-YYYY HH12:MI:SS'", "'dd-mm-yyyy hh12:mi:ss'", "'mm-dd-yyyy hh24:mi:ss'", "'MM-DD-YYYY HH24:MI:SS'", "'mm-dd-yyyy hh12:mi:ss'", "'MM-DD-YYYY HH12:MI:SS'", "'yyyy-mm-dd hh12:mi:ss'", "'YYYY-MM-DD HH12:MI:SS'"};
        String[] replacementList = new String[]{"str_to_date(", "STR_TO_DATE(", "'%Y-%m-%d %H:%i:%s'", "sysdate()", "SYSDATE() ", "'%Y-%m-%d'", "'%d-%m-%Y'", "'%d-%m-%Y'", "'%m-%d-%Y'", "'%m-%d-%Y'", "'%d-%m-%Y %H:%i:%s'", "'%d-%m-%Y %H:%i:%s'", "'%d-%m-%Y %h:%i:%s'", "'%d-%m-%Y %h:%i:%s'", "'%m-%d-%Y %H:%i:%s'", "'%m-%d-%Y %H:%i:%s'", "'%m-%d-%Y %h:%i:%s'", "'%m-%d-%Y %h:%i:%s'", "'%Y-%m-%d %h:%i:%s'", "'%Y-%m-%d %h:%i:%s'"};
        String rtn = StringUtils.replaceEach(source, searchList, replacementList);
        return rtn;
    }

    public static void main(String[] args) throws SQLException {
        IDriverSV driver = DriverFactory.driveOracle();
//		String sql = "SELECT (CASE WHEN COUNT(*)>0 THEN 'true' ELSE 'false' END) flag,holiday from comframe30test.vm_holiday group by holiday";
       /* String sql = "select 'test' from dual";
        ResultSet rs = driver.executeQuery2(sql);
//		rs.next();
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            System.out.print(rsmd.getColumnName(i) + " ");
            System.out.println();
        }*/
        try {
            System.out.print(driver.getSysDate());
        } catch (Exception e) {
            e.printStackTrace();
        }
/*		while(rs.next()){
            System.out.println(rs.getString(1) + " " + rs.getString(2));
//			rs.getString("workflow_id");
		}*/

    }


}
